package Connection;

import BasicClass.Book;
import DAO.BookDao;
import DAO.DaoException;
import javafx.scene.control.Alert;
import javafx.stage.StageStyle;

import java.sql.*;
import java.util.ArrayList;

public class BookDaoImpl implements BookDao {
    static MyConnection Connection;
    public BookDaoImpl(MyConnection myConnection){Connection=myConnection;}

    @Override
    public int addBook(ArrayList<Book> book) throws DaoException {
            String sql = "INSERT INTO books VALUES(?, ?, ?, ?, ?, ?, ?, ?, ?)";
            int count = 0;
            try{
                PreparedStatement stmt = Connection.getConnection().prepareStatement(sql);
                for(Book b:book) {
                    stmt.setString(1, b.getISBN());
                    stmt.setString(2, b.getName());
                    stmt.setString(3, b.getAuthor());
                    stmt.setString(4, b.getPress());
                    stmt.setInt(5, b.getStock());
                    stmt.setDouble(6, b.getPrice());
                    stmt.setString(7, b.getLocation());
                    stmt.setString(8, b.getLabel());
                    stmt.setString(9, b.getRemark());
                    count += stmt.executeUpdate();
                }
            } catch (SQLException ignored) {

            }
            return count;
    }

    @Override
    public void updateBook(Book book) throws DaoException {
        String sql = "UPDATE books SET ";
        sql +=  "B_Name = '"+book.getName()+"', B_Author = '"+book.getAuthor() +"', B_Press = '" +book.getPress()
                +"', B_Stock = "+book.getStock()+", B_Price = "+book.getPrice() +", B_Location = '"+book.getLocation()
                +"', B_Remark = ";
        if(book.getRemark()==null||book.getRemark().equals(""))sql+=null+" WHERE B_ISBN  = '"+book.getISBN()+"'";
        else sql += book.getRemark()+" WHERE B_ISBN  = '"+book.getISBN()+"'";
        try{
            PreparedStatement pstmt = Connection.getConnection().prepareStatement(sql);
            pstmt.executeUpdate();
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }

    @Override
    public void deleteBook(Book book) throws DaoException {
        ArrayList<Book> b = getBook(book, "ISBN号");
        for (Book value : b) {
            if (value.getStock() >= book.getNumber()) {
                book.setStock(value.getStock() - book.getNumber());
                updateBook(book);
            }
        }
    }

    public Book getBookIsbn(String str) throws DaoException{
        String sql = "SELECT * FROM books WHERE B_ISBN = ?";
        ResultSet rst;
        Book book = null;
        try{
            PreparedStatement pstmt = Connection.getConnection().prepareStatement(sql);
            pstmt.setString(1, str);
            rst = pstmt.executeQuery();
            while (rst.next()){
                book = new Book(rst.getString(1),rst.getString(2),rst.getString(3),
                        rst.getString(4),rst.getInt(5),rst.getDouble(6),
                        rst.getString(7),rst.getString(8),rst.getString(9));
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return book;
    }

    @Override
    public ArrayList<Book> getBook(Book book, String manner) throws DaoException {
        ArrayList<Book> books = new ArrayList<>();
        String sql = "SELECT * FROM books ";
        switch (manner) {
            case "书名":
                sql += "WHERE B_Name = ? AND B_Stock > 0";
                break;
            case "作者":
                sql += "WHERE B_Author = ? AND B_Stock > 0";
                break;
            case "ISBN号":
                sql += "WHERE B_ISBN = ? AND B_Stock > 0";
                break;
            default:
        }
        ResultSet rst;
        try{
            PreparedStatement pstmt = Connection.getConnection().prepareStatement(sql);
            switch (manner) {
                case "书名":
                    pstmt.setString(1, book.getName());
                    break;
                case "作者":
                    pstmt.setString(1, book.getAuthor());
                    break;
                case "ISBN号":
                    pstmt.setString(1, book.getISBN());
                    break;
            }
            rst = pstmt.executeQuery();
            while(rst.next()){
                book = new Book(rst.getString(1),rst.getString(2),rst.getString(3),
                        rst.getString(4),rst.getInt(5),rst.getDouble(6),
                        rst.getString(7),rst.getString(8),rst.getString(9));
                books.add(book);
            }
        } catch (SQLException e) {
            throw new RuntimeException(e);
        }
        return books;
    }

    @Override
    public ArrayList<Book> getAllBook() throws DaoException {
        ArrayList<Book> books = new ArrayList<Book>();
        Book book;
        String sql = "SELECT * FROM books WHERE B_Stock > 0";
        ResultSet rst;
        try{
            PreparedStatement pstmt = Connection.getConnection().prepareStatement(sql);
            rst = pstmt.executeQuery();
            while(rst.next()){
                book = new Book(rst.getString(1),rst.getString(2),rst.getString(3),
                        rst.getString(4),rst.getInt(5),rst.getDouble(6),
                        rst.getString(7),rst.getString(8),rst.getString(9));
                books.add(book);
            }
        } catch (SQLException e) {
            throw new RuntimeException(e);
        }
        return books;
    }
}
